﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DBLibrary
{
    public class Customer : PicasaLiteDLL
    {
        protected int id;
        protected string nickname, password;
        protected bool isConnected;

        /// <summary>
        /// Default constructor
        /// </summary>
        public Customer()
            : base()
        {
            id = -1;
            nickname = password = null;
            isConnected = false;
        }

        /// <summary>
        /// Normal constructor
        /// </summary>
        /// <param name="id">ID of the customer</param>
        /// <param name="nickname">nickname of the customer</param>
        /// <param name="password">password of the customer</param>
        public Customer(int id, string nickname, string password)
            : base()
        {
            this.id = id;
            this.nickname = nickname;
            this.password = password;
            this.isConnected = false;
        }

        public int Id
        {
            get { return id; }
        }

        public string Nickname
        {
            get { return nickname; }
        }

        public string Password
        {
            get { return password; }
        }

        public bool IsConnected
        {
            get { return isConnected; }
            set { isConnected = value; }
        }

        /// <summary>
        /// Fill the attribute giving a nickname and a password.
        /// Do nothing if wrong login
        /// </summary>
        /// <param name="nickname"></param>
        /// <param name="password"></param>
        public void connect(string nickname, string password)
        {
            // Check if the user is alreadt connected
            //
            if (isConnected) return;

            try
            {
                // Open the connection to the database
                //
                connection.Open();

                // Build the SQL query
                //
                SqlCommand selectCommand = new SqlCommand("SELECT * FROM Customer WHERE nickname = @nickname AND password = @password", connection);
                selectCommand.Parameters.Add("@nickname", SqlDbType.NVarChar).Value = nickname;
                selectCommand.Parameters.Add("@password", SqlDbType.NVarChar).Value = password;

                // Execute query
                //
                SqlDataReader reader = selectCommand.ExecuteReader();

                // Build the administrator
                //
                while (reader.Read())
                {
                    this.id = (int)reader[0];
                    this.nickname = reader[1].ToString();
                    this.password = reader[2].ToString();
                    this.isConnected = true;
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("An SQL error occured : " + e.Message);
            }
            catch (InvalidOperationException e)
            {
                Console.WriteLine("An error occured while openning the connection : " + e.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Get a specific customer
        /// </summary>
        /// <param name="id">ID of the wanted customer</param>
        /// <returns>Returns the customer matching the ID, null otherwise</returns>
        public Customer getCustomer(int id)
        {
            Customer result = null;
            try
            {
                // Open the connection to the database
                //
                connection.Open();

                // Build the SQL query
                //
                SqlCommand selectCommand = new SqlCommand("SELECT * FROM Customer WHERE id = " + id + " AND is_admin = 0", connection);

                // Execute query
                //
                SqlDataReader reader = selectCommand.ExecuteReader();
                
                // Build the customer
                //
                while (reader.Read())
                {
                    result = new Customer((int)reader[0], reader[1].ToString(), reader[2].ToString());
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("An SQL error occured : " + e.Message);
            }
            catch (InvalidOperationException e)
            {
                Console.WriteLine("An error occured while openning the connection : " + e.Message);
            }
            finally
            {
                connection.Close();
            }

            // Return statement if the customer is not found
            //
            return result;
        }

        private Boolean isUserExists(String nickname)
        {
            SqlCommand selectCommand = new SqlCommand("SELECT id FROM Customer WHERE nickname='" + nickname + "'", connection);

            // Execute Query
            //
            if (selectCommand.ExecuteScalar() == null) return false;
            return true;
        }

        /// <summary>
        /// Add a new customer into the database
        /// </summary>
        /// <param name="nickname">Nickname of the new customer</param>
        /// <param name="password">Password of the new customer</param>
        /// <param name="isAdmin">If the new customer must be registered as admin. Default value : false</param>
        /// <exception cref="InsertException">Exception on insert</exception>
        public void addCustomer(string nickname, string password, bool isAdmin = false)
        {
            try
            {
                // Open the connection to the database
                //
                connection.Open();

                if (isUserExists(nickname)) throw new InsertException("Insert failed. The customer may already exists.");

                // Build the SQL query
                //
                SqlCommand insertCustomer = new SqlCommand("INSERT INTO Customer (id, nickname, password, is_admin) "
                                                            + "VALUES(@id, @nickname, @password, @is_admin)", connection);
                insertCustomer.Parameters.Add("@id", SqlDbType.Int).Value = getNextID("Customer");
                insertCustomer.Parameters.Add("@nickname", SqlDbType.NVarChar).Value = nickname;
                insertCustomer.Parameters.Add("@password", SqlDbType.NVarChar).Value = password;
                insertCustomer.Parameters.Add("@is_admin", SqlDbType.Bit).Value = isAdmin ? 1 : 0;

                // Execute Query
                //
                if (insertCustomer.ExecuteNonQuery() == 0) throw new InsertException("Insert failed. The customer may already exists.");
            }
            catch (SqlException e)
            {
                Console.WriteLine("An SQL error occured : " + e.Message);
            }
            catch (InvalidOperationException e)
            {
                Console.WriteLine("An error occured while openning the connection : " + e.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Get a specific image
        /// </summary>
        /// <param name="id">Image's ID</param>
        /// <returns>Return the byte array of the image giving its ID</returns>
        public byte[] getImage(int id)
        {
            try
            {
                // Open the connection to the database
                //
                connection.Open();

                // Build the SQL query
                //
                SqlCommand selectCommand = new SqlCommand("SELECT blob FROM Image WHERE id = " + id, connection);

                // Execute query
                //
                byte[] image = (byte[])selectCommand.ExecuteScalar();
                return image;
            }
            catch (SqlException e)
            {
                Console.WriteLine("An SQL error occured : " + e.Message);
            }
            catch (InvalidOperationException e)
            {
                Console.WriteLine("An error occured while openning the connection : " + e.Message);
            }
            finally
            {
                connection.Close();
            }

            return null;
        }

        /// <summary>
        /// Get the images' ID for a given album
        /// </summary>
        /// <param name="idAlbum">ID of the album</param>
        /// <returns>Returns a list of all images' ID</returns>
        public List<int> getImageIdByAlbum(int idAlbum)
        {
            List<int> imagesID = new List<int>();

            try
            {
                // Open the connection to the database
                //
                connection.Open();

                // Build the SQL query
                //
                SqlCommand selectCommand = new SqlCommand("SELECT id FROM Image WHERE id_album = " + idAlbum, connection);

                // Execute query
                //
                SqlDataReader reader = selectCommand.ExecuteReader();

                // Build the result list
                //
                while (reader.Read())
                {
                    imagesID.Add((int)reader[0]);
                }
                return imagesID;
            }
            catch (SqlException e)
            {
                Console.WriteLine("An SQL error occured : " + e.Message);
            }
            catch (InvalidOperationException e)
            {
                Console.WriteLine("An error occured while openning the connection : " + e.Message);
            }
            finally
            {
                connection.Close();
            }
            return imagesID;
        }

        /// <summary>
        /// Get all the albums
        /// </summary>
        /// <returns>A list of albums</returns>
        public List<Album> getAllAlbums()
        {
            List<Album> albums = new List<Album>();

            try
            {
                // Open the connection to the database
                //
                connection.Open();

                // Build the SQL query
                //
                SqlCommand selectCommand = new SqlCommand("SELECT * FROM Album", connection);

                // Execute query
                //
                SqlDataReader reader = selectCommand.ExecuteReader();

                // Build the result list
                //
                while (reader.Read())
                {
                    albums.Add(new Album((int)reader[0], (int)reader[1], reader[2].ToString(), Convert.ToDateTime(reader[3].ToString())));
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("An SQL error occured : " + e.Message);
            }
            catch (InvalidOperationException e)
            {
                Console.WriteLine("An error occured while openning the connection : " + e.Message);
            }
            finally
            {
                connection.Close();
            }

            return albums;
        }

        /// <summary>
        /// Get all the customer's albums
        /// </summary>
        /// <returns>Returns a list of albums</returns>
        public List<Album> getCustomersAlbums()
        {
            if (!isConnected) throw new NotConnectedException("You are nor logged. Action denied.");

            List<Album> albums = new List<Album>();

            try
            {
                // Open the connection to the database
                //
                connection.Open();

                // Build the SQL query
                //
                SqlCommand selectCommand = new SqlCommand("SELECT * FROM Album WHERE id_client = " + id, connection);

                // Execute query
                //
                SqlDataReader reader = selectCommand.ExecuteReader();

                // Build the result list
                //
                while (reader.Read())
                {
                    albums.Add(new Album((int)reader[0], (int)reader[1], reader[2].ToString(), Convert.ToDateTime(reader[3].ToString())));
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("An SQL error occured : " + e.Message);
            }
            catch (InvalidOperationException e)
            {
                Console.WriteLine("An error occured while openning the connection : " + e.Message);
            }
            finally
            {
                connection.Close();
            }

            return albums;
        }

        /// <summary>
        /// Add an image into the database
        /// </summary>
        /// <param name="image">Image as byte[]</param>
        /// <param name="idAlbum">Album's ID containing the new Image</param>
        /// <returns>Returns the new image's ID</returns>
        public int addImage(byte[] image, int idAlbum)
        {
            try
            {
                // Open the connection to the database
                //
                connection.Open();

                // Build the SQL query
                //
                int nextID = getNextID("Image");
                SqlCommand insertImage = new SqlCommand("INSERT INTO Image (id, id_album, size, blob) "
                                                            + "VALUES(@id, @id_album, @size, @blob)", connection);
                insertImage.Parameters.Add("@id", SqlDbType.Int).Value = nextID;
                insertImage.Parameters.Add("@id_album", SqlDbType.Int).Value = idAlbum;
                insertImage.Parameters.Add("@size", SqlDbType.Int).Value = image.Length;
                insertImage.Parameters.Add("@blob", SqlDbType.Image).Value = image;

                // Execute Query
                //
                if (insertImage.ExecuteNonQuery() == 0) throw new InsertException("Insert failed. The image's ID may already exists.");
                return nextID;
            }
            catch (SqlException e)
            {
                Console.WriteLine("An SQL error occured : " + e.Message);
            }
            catch (InvalidOperationException e)
            {
                Console.WriteLine("An error occured while openning the connection : " + e.Message);
            }
            finally
            {
                connection.Close();
            }

            return -1;
        }

        /// <summary>
        /// Add an album
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public int addAlbum(String name)
        {
            try
            {
                // Open the connection to the database
                //
                connection.Open();

                // Build the SQL query
                //
                int nextID = getNextID("Album");
                SqlCommand insertAlbum = new SqlCommand("INSERT INTO Album (id, id_client, name, creation_date) "
                                                            + "VALUES(@id, @id_client, @name, @creation_date)", connection);
                insertAlbum.Parameters.Add("@id", SqlDbType.Int).Value = nextID;
                insertAlbum.Parameters.Add("@id_client", SqlDbType.Int).Value = id;
                insertAlbum.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
                insertAlbum.Parameters.Add("@creation_date", SqlDbType.DateTime).Value = DateTime.Now;

                // Execute Query
                //
                if (insertAlbum.ExecuteNonQuery() == 0) throw new InsertException("Insert failed. The album's ID may already exists.");
                return nextID;
            }
            catch (SqlException e)
            {
                Console.WriteLine("An SQL error occured : " + e.Message);
            }
            catch (InvalidOperationException e)
            {
                Console.WriteLine("An error occured while openning the connection : " + e.Message);
            }
            finally
            {
                connection.Close();
            }

            return -1;
        }
    }
}
